package dao.impl;

import dao.BaseDao;
import dao.ClockDao;
import entity.ClockInRecord;

import javax.naming.NamingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

public class ClockDAOImpl extends BaseDao implements ClockDao {

    @Override
    public boolean saveCheckIn(ClockInRecord record) throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement stmt = null;

        try {

            String sql = "INSERT INTO clock_records (student_id, status) VALUES (?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, record.getStudentId());
            stmt.setInt(2, record.getStatus());
            return stmt.executeUpdate() > 0;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public boolean hasCheckedInToday(String studentId) throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {

            String sql = "SELECT COUNT(*) FROM clock_records WHERE student_id = ? AND DATE(clock_time) = CURRENT_DATE()";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, studentId);
            rs = pstmt.executeQuery();
            return rs.next() && rs.getInt(1) > 0;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public List<ClockInRecord> getClockRecords(Date startDate, Date endDate) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<ClockInRecord> records = new ArrayList<>();

        try {

            String sql = "SELECT *, CONVERT_TZ(c.clock_time, '+00:00', '+08:00') AS adjusted_time " +
                    "FROM clock_records c JOIN students s ON c.student_id = s.student_id " +
                    "JOIN dorm_rooms d ON s.room_id = d.room_id ";

            Date adjustedEndDate = endDate;
            if (startDate != null && endDate != null) {
                Calendar cal = Calendar.getInstance();
                cal.setTime(endDate);
                cal.set(Calendar.HOUR_OF_DAY, 23);
                cal.set(Calendar.MINUTE, 59);
                cal.set(Calendar.SECOND, 59);
                adjustedEndDate = cal.getTime();

                sql += "WHERE c.clock_time BETWEEN ? AND ?";
            }

            pstmt = conn.prepareStatement(sql);
            if (startDate != null && endDate != null) {
                pstmt.setTimestamp(1, new Timestamp(startDate.getTime()));
                pstmt.setTimestamp(2, new Timestamp(adjustedEndDate.getTime()));
            }

            rs = pstmt.executeQuery();
            while (rs.next()) {
                ClockInRecord record = new ClockInRecord();
                record.setId(rs.getInt("id"));
                record.setStudentId(rs.getString("student_id"));
                record.setStudentName(rs.getString("name"));
                record.setRoomNumber(rs.getString("room_number"));
                record.setPhone(rs.getString("phone"));
                record.setClockTime(new Timestamp(rs.getTimestamp("adjusted_time").getTime()));
                record.setStatus(rs.getInt("status"));
                records.add(record);
            }
            return records;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}